import com.mysql.jdbc.Driver;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * JDBC 接口
 */
public class Test {
    public static void main(String[] args) throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接
        //预编译 和 防止sql注入 PreparedStatement
        /*
        * select * from tb user where username = ?
        * setString(1,"zhangsan");
        * setString(1,"lisi");
        */
        //&useServerPrepStmts=true 预编译要先开启
        String DB = "tables";
        String url = "jdbc:mysql://localhost:3306/" + DB + "?serverTimezone=GMT%2B8";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);

        //3.定义sql
        /*String sql = """
                insert into users(name, age, status, gender, def_id, managerid, money, entrydate)
                values ('lin1', 20, '1', '男', 3, 2, 4900, '2024-1-30'),
                       ('lin2', 22, '0', '男', 2, 2, 4400, '2024-1-30'),
                       ('lin6', 21, '0', '女', 5, 2, 5200, '2024-1-31');
                """;
*/
        String sql2 = """
                select *
                from users;    
                """;

        //4.获取sql对象
        Statement stmt = conn.createStatement();

        //5.执行sql createStatement(DDL DML) executeQuery(DQL)
        /*int count = stmt.executeUpdate(sql);//受影响的行数*/

        //登录演示
        String name ="方东白";
        String money = "'or'1'='1";
        //防止sql注入
        String sql3 = "select * from users where name = ? and gender = ?";
        PreparedStatement stm = conn.prepareStatement(sql3);
        stm.setString(1, name);
        stm.setString(2,money);
        ResultSet rs = stm.executeQuery();

        if (rs.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }

        stm.close();
        rs.close();


        /*ResultSet pr = stmt.executeQuery(sql2);
        System.out.print("ID ");
        System.out.print("name ");
        System.out.print("age ");
        System.out.print("status ");
        System.out.print("gender ");
        System.out.print("def_id ");
        System.out.print("managerid ");
        System.out.print("money ");
        System.out.println("date ");
        System.out.println("------------------------------------");

        while (pr.next()) {
            int id = pr.getInt(1);
            String name = pr.getString(2);
            int age = pr.getInt(3);
            String  status = pr.getString(4);
            String gender = pr.getString(5);
            int def_id =  pr.getInt(6);
            int managerid =  pr.getInt(7);
            int money = pr.getInt(8);
            Date date = pr.getDate(9);

            System.out.print(id+" ");
            System.out.print(name+" ");
            System.out.print(age+" ");
            System.out.print(status+" ");
            System.out.print(gender+" ");
            System.out.print(def_id+" ");
            System.out.print(managerid+" ");
            System.out.print(money+" ");
            System.out.println(date+" ");
            System.out.println("------------------------------------");
        }*/

        //6.处理结果
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy年MM月dd日 HH:mm:ss");
        LocalDateTime id = LocalDateTime.now();
        //System.out.println("在" + formatter.format(id) + ":\n<" + DB + ">数据库中有" + count + "行受到影响~");


        //7.释放资源
        //pr.close();
        stmt.close();
        conn.close();
    }
}
